﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.Common;

/// <summary>
///UserDB 的摘要说明
/// </summary>
public class DBUser
{
    public DBUser()
    {
    }

    /// <summary>
    /// 插入用户
    /// </summary>
    /// <param name="user">用户数据</param>
    public bool InsertUser(UserData user)
    {
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            DbCommand command = DBConfig.CreateCommand("Insert Into [user](account, code, access) values(@account, @code, @access)");
            command.Connection = conn;
            DbParameter[] parameter = new DbParameter[]
            {
                DBConfig.CreateParameter("@account", user.name),
                DBConfig.CreateParameter("@code", user.password),
                DBConfig.CreateParameter("@access", user.access)
            };
            command.Parameters.AddRange(parameter);
            conn.Open();
            return command.ExecuteNonQuery() > 0;
        }
    }

    /// <summary>
    /// 删除用户
    /// </summary>
    /// <param name="id">标识符</param>
    public bool DeleteUser(int id)
    {
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            DbCommand command = DBConfig.CreateCommand("Delete From [user] Where id=@id");
            command.Connection = conn;
            command.Parameters.Add(DBConfig.CreateParameter("@id", id));
            conn.Open();
            return command.ExecuteNonQuery() > 0;
        }
    }


    /// <summary>
    /// 查找用户信息
    /// </summary>
    /// <param name="id"></param>
    /// <returns></returns>
    public UserData SelectUser(int id)
    {
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            DbCommand command = DBConfig.CreateCommand("Select * From [user]  Where id=@id");
            command.Connection = conn;
            command.Parameters.Add(DBConfig.CreateParameter("@id", id));
            conn.Open();
            DbDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                UserData result = new UserData();
                result.id = Convert.ToInt32(reader["id"]);
                result.name = Convert.ToString(reader["account"]);
                result.password = Convert.ToString(reader["code"]);
                result.access = Convert.ToInt32(reader["access"]);
                reader.Close();
                return result;
            }
            return null;
        }
    }

    /// <summary>
    /// 查找用户信息
    /// </summary>
    /// <param name="account"></param>
    /// <returns></returns>
    public UserData SelectUser(string account)
    {
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            DbCommand command = DBConfig.CreateCommand("Select * From [user]  Where account=@account");
            command.Connection = conn;
            command.Parameters.Add(DBConfig.CreateParameter("@account", account));
            conn.Open();
            DbDataReader reader = command.ExecuteReader();
            if (reader.Read())
            {
                UserData result = new UserData();
                result.id = Convert.ToInt32(reader["id"]);
                result.name = Convert.ToString(reader["account"]);
                result.password = Convert.ToString(reader["code"]);
                result.access = Convert.ToInt32(reader["access"]);
                reader.Close();
                return result;
            }
            return null;
        }
    }

    /// <summary>
    /// 更新用户数据
    /// </summary>
    /// <param name="data">新数据</param>
    public bool UpdateUser(UserData data)
    {
        DbCommand command = DBConfig.CreateCommand("Update [user] Set account=@account, code=@code, access=@access Where id=@id");
        DbParameter[] parameters = new DbParameter[]
        {
            DBConfig.CreateParameter("@account", data.name),
            DBConfig.CreateParameter("@code", data.password),
            DBConfig.CreateParameter("@access", data.access),
            DBConfig.CreateParameter("@id", data.id),
        };
        command.Parameters.AddRange(parameters);
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            command.Connection = conn;
            conn.Open();
            return command.ExecuteNonQuery() > 0;
        }
    }

    public List<UserData> SelectAllUser()
    {
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            DbCommand command = DBConfig.CreateCommand("Select * From [user] order by id");
            command.Connection = conn;
            conn.Open();
            DbDataReader reader = command.ExecuteReader();
            List<UserData> result = new List<UserData>();
            while (reader.Read())
            {
                UserData user = new UserData();
                user.id = Convert.ToInt32(reader["id"]);
                user.name = Convert.ToString(reader["account"]);
                user.password = Convert.ToString(reader["code"]);
                user.access = Convert.ToInt32(reader["access"]);
                result.Add(user);
            }
            reader.Close();
            return result;
        }
    }

    /// <summary>
    /// 删除关联的微博帐号
    /// </summary>
    /// <param name="userId">角色ID</param>
    /// <returns></returns>
    public bool DeleteBlogAccountMapByUserId(int userId)
    {
        DbCommand command = DBConfig.CreateCommand("Delete From [sys_blog_map] Where userId=@userId");
        command.Parameters.Add(DBConfig.CreateParameter("@userId", userId));
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            command.Connection = conn;
            conn.Open();
            return command.ExecuteNonQuery() > 0;
        }
    }

    public bool DeleteBolgAccountMapByBlogId(int blogAccount)
    {
        DbCommand command = DBConfig.CreateCommand("Delete From [sys_blog_map] Where blogAccount=@blogAccount");
        command.Parameters.Add(DBConfig.CreateParameter("@blogAccount", blogAccount));
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            command.Connection = conn;
            conn.Open();
            return command.ExecuteNonQuery() > 0;
        }
    }

    public bool AddBlogAccountMap(int userId, int[] blogAccounts)
    {
        DbCommand command = DBConfig.CreateCommand("Insert Into [sys_blog_map](userId, blogAccount) Values(@userId, @blogAccount)");
        command.Parameters.AddRange(
                       new DbParameter[]
                        {
                            DBConfig.CreateParameter("@userId", userId),
                            DBConfig.CreateParameter("@blogAccount", 0)
                        }
               );
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            conn.Open();
            command.Connection = conn;
            for (int i = 0; i < blogAccounts.Length; i++)
            {
                command.Parameters["@blogAccount"].Value = blogAccounts[i];
                command.ExecuteNonQuery();
            }
            command.Dispose();
            return true;
        }
    }

    public List<BlogAccountData> SelectBlogAccountMap(int userId)
    {
        DbCommand command = DBConfig.CreateCommand("Select blogAccount From [sys_blog_map] Where userId=@userId");
        command.Parameters.Add(DBConfig.CreateParameter("@userId", userId));
        using (DbConnection conn = DBConfig.CreateConnect())
        {
            command.Connection = conn;
            conn.Open();
            DbDataReader reader = command.ExecuteReader();
            List<BlogAccountData> result = null;
            while (reader.Read())
            {
                if (result == null)
                    result = new List<BlogAccountData>();
                DBBlogAccount dbBlog = new DBBlogAccount();
                BlogAccountData account = dbBlog.Select(reader.GetInt32(0));
                if (account != null)
                    result.Add(account);
            }
            reader.Close();
            conn.Close();
            return result;
        }
    }
}